package parser;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DraftGameParser extends BaseParser {
	
	protected Connection connection;

	public static void main(String[] args) {
		DraftGameParser playerScoreParser = new DraftGameParser();
		playerScoreParser.init();
		playerScoreParser.readInPlayersForRound();
	}
	
	public void init() {
		establishConnection();
	}
	
    public void readInPlayersForRound() {
        BufferedReader bufRead = null;
        BufferedReader bufRead2 = null;
        Statement stmt = null;
        
        try {
            stmt = connection.createStatement();
            FileReader input = new FileReader("c:/evoCoach/Players.dat");
            bufRead = new BufferedReader(input);
            FileReader input2 = new FileReader("c:/evoCoach/EvoPositions.dat");
            bufRead2 = new BufferedReader(input2);

            String line = "";
            int count = 0;
            Map<String, String[]> players = new HashMap<String, String[]>();
            while ((line = bufRead.readLine()) != null) {
                players.put(String.valueOf(count++), line.split(","));
            }
            
            int round = 1;
            while ((line = bufRead2.readLine()) != null) {
                String[] elements = line.split(" ");
                String teamIndex = elements[1];
                if (teamIndex.equals("0")) {
                    round++;
                }
                
                String position = "DEF";
                for (int i = 2; i < elements.length; i++) {
                    if (i == 8) {
                        position = "MID";
                    }
                    else if (i == 14) {
                        position = "FWD";
                    }
                    else if (i == 20) {
                        position = "RUCK";
                    }
                    else if (i == 21) {
                        position = "EMG";
                    }
                    if (!elements[i].equals("-1")) {
                        String firstName = players.get(elements[i])[2];
                        String surname = players.get(elements[i])[3];
                        String team = players.get(elements[i])[0];

                        ResultSet rs = stmt.executeQuery("SELECT id from player where firstName = '" + firstName + "' and surname = '" + surname.replace("'", "''") + "' and team = '" + getTeamId(team) + "'");
                        if (!rs.next()) {
                            System.out.println("ERROR ERROR - " + firstName + " " + surname + " " + team);
                        }
                        else {
                            int playerId = rs.getInt("id");
                            String sql = "SELECT id from teamplayer where playerId = " + playerId + " and teamId = " + getEvoTeamId(teamIndex);
                            ResultSet rs2 = stmt.executeQuery(sql);
                            if (rs2.next()) {
                                int teamPlayerId = rs2.getInt("id"); 
                                System.out.println("INSERT INTO playerforround (playerId, round, position) values (" + teamPlayerId + "," + round + ",'" + position + "');");
                            }
                            
                            count++;
                        }
                    }
                }
            }
            
            System.out.println("count - " + count);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                bufRead.close();
                bufRead2.close();                
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
    }	

	public void readInPlayers() {
		BufferedReader bufRead = null;
		FileWriter outFile = null;
		PrintWriter out = null;
		Statement stmt = null;
		List<String> positions = new ArrayList<String>();
		try {
			outFile = new FileWriter("c:/results2.txt");
			out = new PrintWriter(outFile);
			FileReader input = new FileReader("c:/file.txt");
	        bufRead = new BufferedReader(input);

			String line = "";
			int count = 0;
			stmt = connection.createStatement();
			while ((line = bufRead.readLine()) != null) {
				String[] elements = line.split(",");
				String surname = elements[3].replace("'", "''");
				stmt.execute("INSERT into player (firstName, surname, aflClubId, playerNumber, typeId) values ('" + elements[2] + "','" + surname + "'," + getTeamId(elements[0]) + "," + (elements[1].equals("0")?"null":elements[1]) + "," + (elements[7].equals("S")?32:33) + ");");
				ResultSet rs = stmt.executeQuery("SELECT id from player where firstName = '" + elements[2] + "' and surname = '" + surname + "' and aflClubId = " + getTeamId(elements[0]));
				int playerId = -1;
				if (rs.next()) {
					playerId = rs.getInt(1);
				}
				String[] lastYear = elements[4].split(" ");
				String[] thisYear = elements[5].split(" ");
				count++;
				stmt.execute("INSERT into playerposition (playerId, positionId, year) values (" + playerId + "," + getPositionId(lastYear[0]) + ",2011);");
				if (lastYear.length > 1) {
					stmt.execute("INSERT into playerposition (playerId, positionId, year) values (" + playerId + "," + getPositionId(lastYear[1]) + ",2011);");
				}
				if (!thisYear[0].equals(lastYear[0])) {
					if (lastYear.length > 1) {
						if (!thisYear[0].equals(lastYear[1])) {
							stmt.execute("INSERT into playerposition (playerId, positionId, year) values (" + playerId + "," + getPositionId(thisYear[0]) + ",2012);");
						}
					}
					else {
						stmt.execute("INSERT into playerposition (playerId, positionId, year) values (" + playerId + "," + getPositionId(thisYear[0]) + ",2012);");						
					}
				}
				if (thisYear.length > 1) {
					if (!thisYear[1].equals(lastYear[0])) {
						if (lastYear.length > 1) {
							if (!thisYear[1].equals(lastYear[1])) {
								stmt.execute("INSERT into playerposition (playerId, positionId, year) values (" + playerId + "," + getPositionId(thisYear[1]) + ",2012);");
							}
						}
						else {
							stmt.execute("INSERT into playerposition (playerId, positionId, year) values (" + playerId + "," + getPositionId(thisYear[1]) + ",2012);");							
						}
					}
				}
			}
			
			System.out.println("count - " + count);
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				bufRead.close();
				out.close();
				stmt.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public void readInTeamPlayers() {
		
		BufferedReader bufRead = null;
		FileWriter outFile = null;
		PrintWriter out = null;
		Statement stmt = null;
		List<String> positions = new ArrayList<String>();
		try {
			outFile = new FileWriter("c:/results2.txt");
			out = new PrintWriter(outFile);
			FileReader input = new FileReader("c:/file.txt");
	        bufRead = new BufferedReader(input);

			String line = "";
			int count = 0;
			stmt = connection.createStatement();
			int evoTeamId = -1;
			while ((line = bufRead.readLine()) != null) {
				if (!line.startsWith("(")) {
					ResultSet rs = stmt.executeQuery("SELECT id from team where teamName = '" + line.replace("'", "''") + "'");
					if (rs.next()) {
						evoTeamId = rs.getInt(1);
					}					
				}
				else {
					String team = line.substring(1, line.indexOf(")"));
					String name = line.substring(line.indexOf(" ") + 1, line.indexOf("[") - 1);
					String firstName = name.substring(0, name.indexOf(" "));
					String surname = name.substring(name.indexOf(" ") + 1).replace("'", "''");
					ResultSet rs = stmt.executeQuery("SELECT id from player where firstName = '" + firstName + "' and surname = '" + surname + "' and aflClubId = " + getTeamId(team));
					int playerId = -1;
					if (rs.next()) {
						playerId = rs.getInt(1);
					}
					int rookieStatusId = -1;
					if (line.indexOf("First Year Rookie") != -1) {
						rookieStatusId = 48;
					}
					else if (line.indexOf("Second Year Rookie") != -1) {
						rookieStatusId = 49;						
					}
					
					stmt.execute("INSERT into teamplayer (playerId, teamId, rookieStatusId) values (" + playerId + "," + evoTeamId + "," + ((rookieStatusId == -1)?"null":rookieStatusId) + ");");
				}
			}
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				bufRead.close();
				out.close();
				stmt.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
		}
	}	

	private void addPosition(List<String> positions, String reqPosition) {
		boolean foundIt = false;
		for (String position : positions) {
			if (position.equals(reqPosition)) {
				foundIt = true;
				break;
			}
		}
		
		if (!foundIt) {
			positions.add(reqPosition);
		}
	}
	
	private void printPositions(List<String> positions) {
		boolean foundIt = false;
		for (String position : positions) {
			System.out.println("*********************** " + position);
		}
	}
	
	private int getEvoTeamId(String index) {
	    if (index.equals("0")) {
	        return 98;
	    }
	    else if (index.equals("1")) {
            return 99;
        }
	    else if (index.equals("2")) {
            return 100;
        }
	    else if (index.equals("3")) {
            return 101;
        }
	    else if (index.equals("4")) {
            return 102;
        }
	    else if (index.equals("5")) {
            return 103;
        }
	    else if (index.equals("6")) {
            return 104;
        }
	    else if (index.equals("7")) {
            return 105;
        }
	    else if (index.equals("8")) {
            return 106;
        }
	    else if (index.equals("9")) {
            return 107;
        }
	    else if (index.equals("10")) {
            return 108;
        }
	    else if (index.equals("11")) {
            return 109;
        }
	    else if (index.equals("12")) {
            return 110;
        }
	    else if (index.equals("13")) {
            return 111;
        }
	    else if (index.equals("14")) {
            return 112;
        }
	    else {
            return 113;
        }
	}
	
	private String getTeamId(String team) {
		if (team.equals("ADL")) {
			return "ADELAIDE";
		}
		else if (team.equals("BRIS")) {
			return "BRISBANE";
		}
		else if (team.equals("CARL")) {
			return "CARLTON";
		}
		else if (team.equals("COL")) {
			return "COLLINGWOOD";
		}
		else if (team.equals("ESS")) {
			return "ESSENDON";
		}
		else if (team.equals("FREO")) {
			return "FREMANTLE";
		}
		else if (team.equals("GEEL")) {
			return "GEELONG";
		}
		else if (team.equals("GC")) {
			return "GC";
		}
		else if (team.equals("GWS")) {
			return "GWS";
		}
		else if (team.equals("HAW")) {
			return "HAWTHORN";
		}
		else if (team.equals("MELB")) {
			return "MELBOURNE";
		}
		else if (team.equals("NTH")) {
			return "NORTH_MELBOURNE";
		}
		else if (team.equals("PORT")) {
			return "PORT_ADELAIDE";
		}
		else if (team.equals("RICH")) {
			return "RICHMOND";
		}
		else if (team.equals("STK")) {
			return "ST_KILDA";
		}
		else if (team.equals("SYD")) {
			return "SYDNEY";
		}
		else if (team.equals("WCE")) {
			return "WCE";
		}		
		else {
			return "WESTERN_BULLDOGS";
		}
	}
	
	private int getPositionId(String position) {
		if (position.equals("DEF")) {
			return 42;
		}
		else if (position.equals("MID")) {
			return 43;
		}
		else if (position.equals("FWD")) {
			return 44;
		}
		else if (position.equals("RUCK")) {
			return 45;
		}
		else if (position.equals("UNAVAILABLE")) {
			return 46;
		}		
		else {
			return 47;
		}
	}
	
	private void establishConnection() {
		try {
			Class.forName( "com.mysql.jdbc.Driver" );
			connection = DriverManager.getConnection(
				     "jdbc:mysql://localhost/evoCoach",
				     "root",
				     "password" );

		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
	
}
